﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Extend.EnumUtil;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Estsh.Core.Wms.Repositories
{
	public class ProductionReportingRepository : BaseRepository<BaseEntity>, IProductionReportingRepository
	{

		public ProductionReportingRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
		{

		}



		/// <summary>
		/// 根据登录账号获取工作中心
		/// </summary>
		/// <param name="empId"></param>
		/// <returns></returns>
		public List<Syspdline_Extend> GetSysPdlineList(string empId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				//SqlStringBuilder.Append("SELECT  pdline_id,pdline_code,pdline_name FROM dbo.sys_pdline  (NOLOCK)  ");
				//            SqlStringBuilder.Append(" WHERE enabled='Y'  ");
				//SqlStringBuilder.Append(" ORDER BY pdline_name desc ");

				SqlStringBuilder.Append(" SELECT * FROM dbo.sys_pdline pd (nolock) WHERE pd.pdline_name IN  (SELECT a.name  from dbo.sys_web_menu as A (nolock) ");
				SqlStringBuilder.Append(" INNER join dbo.sys_role_privliege as B (nolock)  on cast(a.menu_id  as nvarchar)=b.fun_name ");
				SqlStringBuilder.Append(" INNER join sys_role_emp as D (nolock)  on d.role_id=b.role_id ");
				SqlStringBuilder.Append(" INNER join sys_emp as E (nolock)  on e.emp_id=d.emp_id ");
				SqlStringBuilder.Append(" where  e.emp_id='"+ empId + "' AND a.parent_id=60 AND a.sort_num>0 AND a.name!='PDA' AND a.enabled='Y'  ) AND pd.enabled='Y' ORDER BY pd.pdline_name ");

				List <Syspdline_Extend> result = dbConn.Query<Syspdline_Extend>(SqlStringBuilder.ToString()).ToList();

				return result;
			}
		}

		/// <summary>
		/// 根据工作中心来查询工单
		/// </summary>
		/// <param name="pdlineId"></param>
		/// <returns></returns>
		public List<MesWorkOrder_Extend> GetWorkOrderByPdlineId(string pdlineId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append(" SELECT w.* FROM dbo.mes_work_order w  (NOLOCK)  LEFT JOIN dbo.sys_pdline p  (NOLOCK) ON w.pdline_id=p.pdline_id  ");
				SqlStringBuilder.Append(" WHERE w.enabled='Y'AND p.enabled='Y'  and w.order_status=30 ");// AND w.order_status=10
				if (pdlineId.Length > 0)
				{
					SqlStringBuilder.Append(" AND w.pdline_id='" + pdlineId + "' ORDER BY w.produce_time ASC ");
				}

				List<MesWorkOrder_Extend> result = dbConn.Query<MesWorkOrder_Extend>(SqlStringBuilder.ToString()).ToList();

				return result;
			}
		}

		/// <summary>
		/// 根据工单、产线来查询 
		/// </summary>
		/// <param name="pdlineId"></param>
		/// <param name="orderNo"></param>
		/// <returns></returns>
		public List<SysEmpPdline> GetEmPdlineByOrderNoAndPdline(string pdlineId, string orderNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append(" SELECT * FROM dbo.sys_emp_pdline (NOLOCK) WHERE enabled='Y' AND pdline_id='" + pdlineId + "' AND order_no='" + orderNo + "' ; ");
				List<SysEmpPdline> empPdlineInfos = dbConn.Query<SysEmpPdline>(SqlStringBuilder.ToString()).ToList();//产线和员工的关系
				return empPdlineInfos;
			}

		}


		/// <summary>
		/// 保存人员与产线的关系
		/// </summary>
		/// <param name="empNo"></param>
		/// <returns></returns>
		public int SaveEmpByPdline(string empNo, string pdlineId, string orderNo, string loginId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append(" SELECT * FROM dbo.sys_emp (NOLOCK) WHERE enabled='Y' AND emp_no='"+ empNo + "';");//人员信息
				List<SysEmp>  empinfos = dbConn.Query<SysEmp>(SqlStringBuilder.ToString()).ToList();
				if (empinfos.Count > 0)
				{
					#region 判断是员工是否有权限
					List<Syspdline_Extend> pdlines= GetSysPdlineList(empinfos[0].EmpId.ToString());
					if (pdlines.Count() == 0)
					{
						return -2;
					}

					if (pdlines.Where(s => s.PdlineId == Convert.ToInt32(pdlineId)).ToList().Count() == 0)
					{
						return -2;
					}

					#endregion



					SqlStringBuilder = new StringBuilder(1024);
					SqlStringBuilder.Append(" SELECT * FROM dbo.sys_pdline (NOLOCK) WHERE enabled='Y' AND pdline_id='"+ pdlineId + "' ;");
					List<Syspdline_Extend> syspdlineInfos = dbConn.Query<Syspdline_Extend>(SqlStringBuilder.ToString()).ToList();//产线

					List<string> sqlStrings = new List<string>();
					SqlStringBuilder = new StringBuilder(1024);

					#region 员工变更
					//如果 在产线和工单下，已有扫描的员工工号，删除员工与产线的关系
					SqlStringBuilder = new StringBuilder(1024);
					SqlStringBuilder.Append(" SELECT * FROM dbo.sys_emp_pdline (NOLOCK) WHERE enabled='Y' AND pdline_id='"+ pdlineId + "' AND order_no='"+ orderNo + "' AND emp_no='"+ empNo + "'; ");
					List<SysEmpPdline> empPdlineInfos = dbConn.Query<SysEmpPdline>(SqlStringBuilder.ToString()).ToList();//产线和员工的关系
					if (empPdlineInfos.Count() > 0)//执行删除操作
					{
						SqlStringBuilder = new StringBuilder(1024);
						SqlStringBuilder.Append(" UPDATE dbo.sys_emp_pdline SET  enabled='N',update_userid='"+ loginId + "' " +"  , update_time='"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' ");
						SqlStringBuilder.Append(" WHERE enabled='Y' AND pdline_id='"+ pdlineId + "' AND order_no='"+ orderNo + "' AND emp_no='"+ empNo + "' ; ");
						sqlStrings.Add(SqlStringBuilder.ToString());
						
					}
					else {

						#region 添加人员与产线的关系表
						SqlStringBuilder = new StringBuilder(1024);
						SqlStringBuilder.Append(" INSERT INTO dbo.sys_emp_pdline (  ");
						SqlStringBuilder.Append(" [emp_id], [emp_no], [emp_name],   ");
						SqlStringBuilder.Append(" [pdline_id], [pdline_name], [order_no],   ");
						SqlStringBuilder.Append(" [enabled], [create_userid], [create_time], [guid])   ");
						SqlStringBuilder.Append(" VALUES(   ");
						SqlStringBuilder.Append(" " + empinfos[0].EmpId + ", '" + empinfos[0].EmpNo + "', N'" + empinfos[0].EmpName + "', ");//员工id、工号、姓名
						SqlStringBuilder.Append(" " + syspdlineInfos[0].PdlineId + ", '" + syspdlineInfos[0].PdlineName + "', '" + orderNo + "',  ");//工作中心id、名称、工单号
						SqlStringBuilder.Append(" 'Y', " + loginId + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' ,'" + Guid.NewGuid() + "' ) ;  ");//创建人、创建时间、

						sqlStrings.Add(SqlStringBuilder.ToString());

						#endregion

					}
					//处理数据
					int isSuccess = SQLTransaction(sqlStrings, null);
					if (isSuccess == 1)
					{
						return 1;
					}
					return 0;

					#endregion



				}
				else
				{
					return -1;
				}
			}
		}


		/// <summary>
		///根据工单零件判断是否是成品
		/// </summary>
		/// <param name="jobj"></param>
		/// <returns></returns>
		public List<SysPart_Extend> GetworkOrderByPartType(string partId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append(" SELECT part_no,part_type FROM dbo.sys_part (NOLOCK) WHERE enabled='Y' AND part_id='"+ partId + "' ; ");
				List<SysPart_Extend> partInfos = dbConn.Query<SysPart_Extend>(SqlStringBuilder.ToString()).ToList();//产线和员工的关系
				return partInfos;
			}

		}


		/// <summary>
		///根据扫描的周转箱，判断它是否存在
		/// </summary>
		/// <param name="jobj"></param>
		/// <returns></returns>
		public List<SysStock_Extend> GetSysStockByCartonNo(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.sys_stock (NOLOCK) WHERE enabled='Y' AND carton_type=1 AND carton_no='" + cartonNo+"'; ");//判断周转箱
				List<SysStock_Extend> sysStockInfos = dbConn.Query<SysStock_Extend>(SqlStringBuilder.ToString()).ToList();
				return sysStockInfos;
			}

		}

		/// <summary>
		///根据扫描的周转箱，是否存在关联的箱条码
		/// </summary>
		/// <param name="jobj"></param>
		/// <returns></returns>
		public List<WmsRackPackage_Extend> GetWmsRackByCartonNo(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.wms_rack_package WHERE rack_no='"+ cartonNo + "' AND enabled='Y' AND packageType=1;");//判断周转箱
				List<WmsRackPackage_Extend> Infos = dbConn.Query<WmsRackPackage_Extend>(SqlStringBuilder.ToString()).ToList();
				return Infos;
			}

		}

		/// <summary>
		/// 查询模具号
		/// </summary>
		/// <param name="modelNo"></param>
		/// <returns></returns>
		public List<SysEquipment> GetEquipmentByModelNo(string modelNo)
		{

			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.sys_equipment (NOLOCK) WHERE  enabled='Y' and equipment_no='" + modelNo + "'; ");
				List<SysEquipment> sysEquipmentInfos = dbConn.Query<SysEquipment>(SqlStringBuilder.ToString()).ToList();
				return sysEquipmentInfos;
			}

		}
		/// <summary>
		/// 查询产线
		/// </summary>
		/// <param name="pdlineId"></param>
		/// <returns></returns>
		public List<Syspdline_Extend> GetPdlineByPdlineId(string pdlineId)
		{

			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.sys_pdline (NOLOCK) WHERE enabled='Y' and pdline_id='"+ pdlineId + "';");
				List<Syspdline_Extend> syspdlineInfos = dbConn.Query<Syspdline_Extend>(SqlStringBuilder.ToString()).ToList();
				return syspdlineInfos;
			}

		}

		/// <summary>
		/// 根据工单号和产线来查询工单
		/// </summary>
		/// <returns></returns>
		public List<MesWorkOrder_Extend> GetWorkOrderByOrderNoByPdlineId(string orderNo, string pdlineId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.mes_work_order (NOLOCK) WHERE enabled='Y' AND order_no='"+ orderNo + "' AND pdline_id='"+ pdlineId + "' AND order_status='"+(int)EnumUtil_Extend.MesWorkOrderStatus.FIRING + "' ;  ");// 状态为开启状态
				List<MesWorkOrder_Extend> result = dbConn.Query<MesWorkOrder_Extend>(SqlStringBuilder.ToString()).ToList();
				return result;
			}
		}

		/// <summary>
		/// 根据工单中零件查询bom明细
		/// </summary>
		/// <returns></returns>
		public List<SysBomDetail> GetBomDetailByPartId(string partId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("   SELECT  * FROM dbo.sys_bom_detail d WHERE d.part_id='" + partId + "' AND d.enabled='Y'   ;  ");
				List<SysBomDetail> result = dbConn.Query<SysBomDetail>(SqlStringBuilder.ToString()).ToList();
				return result;
			}
		}

		/// <summary>
		/// 根据零件id查询零件明细
		/// </summary>
		/// <returns></returns>
		public List<SysPart_Extend> GetSysPartByPartId(string partId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT * FROM dbo.sys_part WHERE enabled='Y' AND  part_id='"+ partId + "' ;");
				List<SysPart_Extend> result = dbConn.Query<SysPart_Extend>(SqlStringBuilder.ToString()).ToList();
				return result;
			}
		}

		/// <summary>
		/// 根据工作中心、工单号来查询工单
		/// </summary>
		/// <param name="pdlineId"></param>
		/// <returns></returns>
		public List<MesWorkOrder_Extend> GetWorkOrderByPdlineIdAndOrderNo(string pdlineId,string orderNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("  SELECT w.* FROM dbo.mes_work_order w  (NOLOCK)  LEFT JOIN dbo.sys_pdline p  (NOLOCK) ON w.pdline_id=p.pdline_id  ");
				SqlStringBuilder.Append("  WHERE w.enabled='Y' AND p.enabled='Y'  AND  w.pdline_id='" + pdlineId + "' AND  w.order_no='"+ orderNo + "'   AND w.order_status  IN ( '30','50')   ");
				SqlStringBuilder.Append("  ORDER BY w.produce_time ASC  ");

				List<MesWorkOrder_Extend> result = dbConn.Query<MesWorkOrder_Extend>(SqlStringBuilder.ToString()).ToList();

				return result;
			}
		}




		/// <summary>
		/// 判断零件是否在免检清单
		/// </summary>
		/// <returns></returns>
		public List<SysExemptionList> Getqc()
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				//查询免检物料清单
				StringBuilder exemptionString = new StringBuilder(1024);
				exemptionString.Append(" select part_no from sys_exemption_list  where enabled='Y' ");
				List<SysExemptionList> result = dbConn.Query<SysExemptionList>(exemptionString.ToString()).ToList();
				return result;
			}
		}

		/// <summary>
		/// 验证周转箱是否在关系表中存在
		/// </summary>
		/// <param name="rackNo"></param>
		/// <returns></returns>
		public List<WmsRack_Extend> GetWmsRack(string rackNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder exemptionString = new StringBuilder(1024);
				exemptionString.Append(" SELECT * FROM wms_rack WHERE enabled='Y'  AND  packageType=1  AND rack_no='" + rackNo + "' ");
				List<WmsRack_Extend> result = dbConn.Query<WmsRack_Extend>(exemptionString.ToString()).ToList();
				return result;
			}
		}

		/// <summary>
		/// 查询workOrder表生成质检单
		/// </summary>
		/// <param name="orderNo">工单号</param>
		/// <returns></returns>
		public List<GWorkOrderSn_Extend> GetGWrokOrderSn(string orderNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder exemptionString = new StringBuilder(1024);
				exemptionString.Append("  SELECT * FROM dbo.g_workorder_sn WHERE enabled='Y' AND workorder='"+ orderNo + "'  ORDER BY ruid ASC ");
				List<GWorkOrderSn_Extend> result = dbConn.Query<GWorkOrderSn_Extend>(exemptionString.ToString()).ToList();
				return result;
			}
		}


		/// <summary>
		/// 根据登录账号获取按钮
		/// </summary>
		/// <param name="empId"></param>
		/// <returns></returns>
		public List<SysProgramFunOp_Extend> getProgramFunOpListByUser(int userId, int menuId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT o.* FROM dbo.sys_program_fun_op o  ");
                SqlStringBuilder.Append(" JOIN sys_role_op_privliege pp ON o.ruid = pp.op_id  ");
                SqlStringBuilder.Append(" JOIN sys_role_emp re ON pp.role_id = re.role_id WHERE   re.emp_id = '"+ userId + "'   ");
                SqlStringBuilder.Append(" AND o.enabled = 'Y'  AND o.fun_name='"+ menuId + "' ORDER BY o.sort_num ");
               
                List<SysProgramFunOp_Extend> result = dbConn.Query<SysProgramFunOp_Extend>(SqlStringBuilder.ToString()).ToList();

                return result;
            }
        }


		//事务批量执行添加、修改
		public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				if (dbConn.State == ConnectionState.Closed)
				{
					dbConn.Open();
				}
				//执行事务
				IDbTransaction transaction = dbConn.BeginTransaction();
				if (parameterList == null || parameterList.Count == 0)
				{
					try
					{
						for (int i = 0; i < sqlStrings.Count; i++)
						{
							dbConn.Execute(sqlStrings[i], null, transaction);
						}
						transaction.Commit();
						return 1;
					}
					catch (Exception exception)
					{
						transaction.Rollback();
						return 0;
					}
				}
				else
				{
					try
					{
						for (int i = 0; i < sqlStrings.Count; i++)
						{
							dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
						}
						transaction.Commit();
						return 1;
					}
					catch (Exception exception)
					{
						transaction.Rollback();
						return 0;
					}
				}
			}
		}


		/// <summary>
		/// 生成订单编号
		/// </summary>
		/// <returns></returns>
		public string GetOrderNo(string stockOrder, string p)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				DynamicParameters list = new DynamicParameters();
				list.Add("@order_type", stockOrder);
				list.Add("@order_prefix", p);
				list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

				var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);// this._remotingProxy.ExecuteSotreProcedure("dbo.sys_create_orderno", list);
				string result = list.Get<string>("@order_no");
				return result;
			}
		}


	}
}
